package br.cefetrn.banco;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import br.cefetrn.dominio.Beneficiario;
import br.cefetrn.dominio.Declaracao;
import br.cefetrn.fachada.FachadaBeneficiario;

public class BeneficiarioDAO implements FachadaBeneficiario {

	public int insertBeneficiario(Beneficiario b, Declaracao d) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int cont = 0;
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("insert into beneficiario (nome, cpf, previdencia_social, previdencia_privada, " +
            		"pensao_alimenticia, aposentadoria, diarias, invalidez, lucro_de_pj, valor_titular_microempresa, indenizacoes, " +
            		"outros_rendimentos_isentos, cpfcnpj) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            stmt.setString(1, b.getNome());
            stmt.setString(2, b.getCPF());
            stmt.setDouble(3, b.getPrevidencia_social());
            stmt.setDouble(4, b.getPrevidencia_privada());
            stmt.setDouble(5, b.getPensao_alimenticia());
            stmt.setDouble(6, b.getAposentadoria());
            stmt.setDouble(7, b.getDiarias());
            stmt.setDouble(8, b.getInvalidez());
            stmt.setDouble(9, b.getLucro_de_pj());
            stmt.setDouble(10, b.getValor_titular_microempresa());
            stmt.setDouble(11, b.getIndenizacoes());
            stmt.setDouble(12, b.getOutros_rendimentos_isentos());
            stmt.setString(13, d.getCpfcnpj());
            stmt.executeUpdate();
            cont = 1;
        }
        catch (SQLException ex) {
            cont = 0;
            ex.printStackTrace();
        }
        finally {
        System.out.println("entrou no finally do banco");
        Banco.closeConexao(conn, rs, stmt);
        return cont;
        }
    }
	
	@SuppressWarnings("finally")
	public Collection selectBeneficiario() throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Collection res = new ArrayList();
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select * from beneficiario");
            rs = stmt.executeQuery();
            while (rs.next()){
                Beneficiario b = new Beneficiario();
                b.setNome(rs.getString("nome"));
                b.setCPF(rs.getString("cpf"));
                b.setAposentadoria(rs.getDouble("aposentadoria"));
                b.setDiarias(rs.getDouble("diarias"));
                b.setIndenizacoes(rs.getDouble("indenizacoes"));
                b.setInvalidez(rs.getDouble("invalidez"));
                b.setLucro_de_pj(rs.getDouble("lucro_de_pj"));
                b.setOutros_rendimentos_isentos(rs.getDouble("outros_rendimentos_isentos"));
                b.setPensao_alimenticia(rs.getDouble("pensao_alimenticia"));
                b.setPrevidencia_privada(rs.getDouble("previdencia_privada"));
                b.setPrevidencia_social(rs.getDouble("previdencia_social"));
                b.setValor_titular_microempresa(rs.getDouble("valor_titular_microempresa"));
                res.add(b);
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;        
        }
    }
	
	@SuppressWarnings("finally")
	public Collection selectBeneficiario_Declaracao(String cpfcnpj) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Collection res = new ArrayList();
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select * from beneficiario where cpfcnpj = ?");
            stmt.setString(1, cpfcnpj);
            rs = stmt.executeQuery();
            while (rs.next()){
                Beneficiario b = new Beneficiario();
                b.setNome(rs.getString("nome"));
                b.setCPF(rs.getString("cpf"));
                b.setAposentadoria(rs.getDouble("aposentadoria"));
                b.setDiarias(rs.getDouble("diarias"));
                b.setIndenizacoes(rs.getDouble("indenizacoes"));
                b.setInvalidez(rs.getDouble("invalidez"));
                b.setLucro_de_pj(rs.getDouble("lucro_de_pj"));
                b.setOutros_rendimentos_isentos(rs.getDouble("outros_rendimentos_isentos"));
                b.setPensao_alimenticia(rs.getDouble("pensao_alimenticia"));
                b.setPrevidencia_privada(rs.getDouble("previdencia_privada"));
                b.setPrevidencia_social(rs.getDouble("previdencia_social"));
                b.setValor_titular_microempresa(rs.getDouble("valor_titular_microempresa"));
                res.add(b);
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;        
        }
    }
	
}

